-- @owner: Alice
-- @date: 2025-09-03
-- @testpoint: 确定性函数支持result_cache,函数入参为smallint、varchar，合理报错
@conn PrimaryDbAmode;
drop extension if exists gms_output;
drop table if exists all_support_type;
drop function if exists func_smallint;
drop function if exists func_smallint_cache;
drop function if exists func_smallint_nopara;
drop function if exists func_smallint_cache;
drop function if exists func_varchar_cache;
drop function if exists func_int_cache;
drop function if exists func_bigint_cache;
drop function if exists func_number_cache;
drop function if exists func_numeric_cache;
drop function if exists func_float_cache;
drop function if exists func_double_precision_cache;
drop function if exists func_char_cache;
drop function if exists func_varchar2_cache;
drop function if exists func_nvarchar2_cache;
drop function if exists func_text_cache;
drop function if exists func_bool_cache;
drop function if exists func_timestamp_cache;
drop function if exists func_smallint_cache;
drop function if exists taxi_accum ;
drop function if exists pg_concat;
drop function if exists pg_concat_fin;
drop function if exists para_cache ;
drop function if exists para_cache ;
drop function if exists var_test1;
drop function if exists func_smallint_cache;
drop function if exists tran_update_func;
drop function if exists factorial(integer);
drop function if exists f_test;
drop function if exists pg_concat(text) cascade;
drop function if exists pg_concat(text,text) cascade;
create extension gms_output;
--result_cache
--会话需要设置这个值会输出debug信息
set enable_func_cache = 1;
select gms_output.enable();
set query_dop=1;
set datestyle='ISO,MDY';
--创建表及函数
drop table if exists all_support_type;
create table all_support_type(
id int,
col_bool bool,
col_smallint smallint,
col_int int,
col_bigint bigint,
col_float float,
col_double_precision double precision,
col_char char(720),
col_varchar varchar(920),
col_varchar2 varchar2(20),
col_timestamp timestamp,
col_numeric numeric,
col_number number,
col_text text
);
insert into all_support_type values( 1, 'f',1,1,1,1,1,'1_haha','0_haha','1_haha','2024-11-22 16:36:45',1,1,'0_haha');
insert into all_support_type values( 2, 'f',2,2,2,2,2,'0_haha','1_haha','1_haha','2024-11-22 16:36:45',2,2,'0_haha');
insert into all_support_type values( 3, 'f',0,0,0,0,0,'0_haha','1_haha','0_haha','2024-11-22 16:36:45',0,0,'0_haha');
insert into all_support_type values( 4, 'f',1,1,1,1,1,'1_haha','1_haha','1_haha','2024-11-22 16:36:45',1,1,'1_haha');
insert into all_support_type values( 5, 'f',2,2,2,2,2,'0_haha','1_haha','1_haha','2024-11-22 16:36:45',2,2,'0_haha');
insert into all_support_type values( 6, 'f',0,0,0,0,0,'0_haha','1_haha','0_haha','2024-11-22 16:36:45',0,0,'1_haha');
insert into all_support_type values( 7, 'f',1,1,1,1,1,'0_haha','0_haha','1_haha','2024-11-22 16:36:45',1,1,'1_haha');
insert into all_support_type values( 8, 'f',2,2,2,2,2,'0_haha','0_haha','1_haha','2024-11-22 16:36:45',2,2,'0_haha');
insert into all_support_type values( 9, 'f',0,0,0,0,0,'0_haha','0_haha','1_haha','2024-11-22 16:36:45',0,0,'0_haha');
insert into all_support_type values(10, 'f',1,1,1,1,1,'0_haha','1_haha','0_haha','2024-11-22 16:36:45',1,1,'1_haha');
insert into all_support_type values(11, 'f',2,2,2,2,2,'0_haha','0_haha','0_haha','2024-11-22 16:36:45',2,2,'0_haha');
insert into all_support_type values(12, 'f',0,0,0,0,0,'0_haha','0_haha','0_haha','2024-11-22 16:36:45',0,0,'0_haha');
insert into all_support_type values(13, 'f',1,1,1,1,1,'0_haha','1_haha','1_haha','2024-11-22 16:36:45',1,1,'0_haha');
insert into all_support_type values(14, 'f',2,2,2,2,2,'0_haha','0_haha','1_haha','2024-11-22 16:36:45',2,2,'1_haha');
insert into all_support_type values(15, 'f',0,0,0,0,0,'1_haha','0_haha','1_haha','2024-11-22 16:36:45',0,0,'0_haha');
insert into all_support_type values(16, 'f',1,1,1,1,1,'0_haha','1_haha','1_haha','2024-11-22 16:36:45',1,1,'1_haha');
insert into all_support_type values(17, 'f',2,2,2,2,2,'1_haha','0_haha','0_haha','2024-11-22 16:36:45',2,2,'0_haha');
insert into all_support_type values(18, 'f',0,0,0,0,0,'1_haha','1_haha','1_haha','2024-11-22 16:36:45',0,0,'0_haha');
insert into all_support_type values(19, 'f',1,1,1,1,1,'0_haha','0_haha','1_haha','2024-11-22 16:36:45',1,1,'0_haha');
insert into all_support_type values(20, 'f',2,2,2,2,2,'1_haha','1_haha','0_haha','2024-11-22 16:36:45',2,2,'0_haha');
insert into all_support_type(id) values(21);
insert into all_support_type(id) values(22);
insert into all_support_type values(23,'t',1111,1111,1111,1111,1111,'aasddffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff','aaaaaa','fffff','1997-1-1 10:00:00',1,1,'3345');
insert into all_support_type values(24,'',1111,1111,1111,1111,1111,'','','','',1,1,'');
select count(*) from (select * from all_support_type order by id) ;
--create function
create or replace function func_smallint(p smallint) returns smallint as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
gms_output.put_line('param:'||p);
end if;
return p+1;
end;
$$ language plpgsql stable;
/
create or replace function func_smallint_cache(p smallint) returns smallint as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
gms_output.put_line('param:'||p);
end if;
return p+1;
end;
$$ language plpgsql stable result_cache;
/
create or replace function func_smallint_nopara() returns smallint as $$
declare
p int=2;
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
gms_output.put_line('param:'||p);
end if;
return p+1;
end;
$$ language plpgsql stable result_cache;
/
--执行查询
explain(costs off,analyze,timing off)
select
col_smallint,func_smallint(col_smallint)
from (select * from all_support_type order by id) ;
explain(costs off,analyze,timing off)
select
col_smallint,func_smallint_cache(col_smallint)
from (select * from all_support_type order by id) ;
explain(costs off,analyze,timing off)
select
col_smallint,func_smallint_nopara()
from (select * from all_support_type order by id) ;
select col_smallint,func_smallint_cache(col_smallint) from (select * from all_support_type order by id) ;
select id,col_smallint,func_smallint_cache(col_smallint) from (select * from all_support_type order by id) ;
create or replace function func_smallint_cache(p smallint) returns smallint as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
gms_output.put_line('param:'||p);
end if;
return p+1;
end;
$$ language plpgsql stable result_cache;
/
explain(costs off,analyze,timing off)
select func_smallint_cache(1);
--case2
create or replace function func_varchar_cache(p varchar(30)) returns varchar as $$
begin
raise notice 'NOTICE: excute function...';
if p = '1_haha' then
gms_output.put_line('param:'||p);
end if;
return 'cache1';
end;
$$ language plpgsql stable result_cache;
/
explain(costs off,analyze,timing off)
select
col_varchar,func_varchar_cache(col_varchar)
from (select * from all_support_type order by id) ;
insert into all_support_type(id,col_varchar) values(11,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx1');
insert into all_support_type(id,col_varchar) values(12,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx1');
insert into all_support_type(id,col_varchar) values(100,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx1');
insert into all_support_type(id,col_varchar) values(101,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx1');
explain(costs off,analyze,timing off)
select
func_varchar_cache('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx1')
;
explain(costs off,analyze,timing off)
select
func_varchar_cache('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxx1')
;
select
func_varchar_cache('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxx1')
from (select * from all_support_type order by id) ;
select
func_varchar_cache('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx1')
from (select * from all_support_type order by id) ;
--create function
--int
create or replace function func_int_cache(p int) returns bigint as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
gms_output.put_line('param:'||p);
end if;
return p+1;
end;
$$ language plpgsql stable result_cache;
/
select func_int_cache(12) from (select * from all_support_type order by id) ;
select func_int_cache(col_int) from (select * from all_support_type order by id) ;
--bigint
create or replace function func_bigint_cache(p bigint) returns bigint as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
gms_output.put_line('param:'||p);
end if;
return p+1;
end;
$$ language plpgsql stable result_cache;
/
select func_bigint_cache(12) from (select * from all_support_type order by id) ;
select func_bigint_cache(col_bigint) from (select * from all_support_type order by id) ;
--number
create or replace function func_number_cache(p number) returns number as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
gms_output.put_line('param:'||p);
end if;
return p+1;
end;
$$ language plpgsql stable result_cache;
/
select func_number_cache(12) from (select * from all_support_type order by id) ;
select col_number,func_number_cache(col_number) from (select * from all_support_type order by id) ;
--numeric
create or replace function func_numeric_cache(p numeric) returns numeric as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
gms_output.put_line('param:'||p);
end if;
return p+1;
end;
$$ language plpgsql stable result_cache;
/
select func_numeric_cache(12) from (select * from all_support_type order by id) ;
select col_numeric,func_numeric_cache(col_numeric) from (select * from all_support_type order by id) ;
--float
create or replace function func_float_cache(p float) returns float as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
gms_output.put_line('param:'||p);
end if;
return p+1;
end;
$$ language plpgsql stable result_cache;
/
select func_float_cache(12) from (select * from all_support_type order by id) ;
select col_float,func_float_cache(col_float) from (select * from all_support_type order by id) ;
--double precision
create or replace function func_double_precision_cache(p double precision) returns double precision as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
gms_output.put_line('param:'||p);
end if;
return p+1;
end;
$$ language plpgsql stable result_cache;
/
select func_double_precision_cache(12) from (select * from all_support_type order by id) ;
select col_double_precision,func_double_precision_cache(col_double_precision) from (select * from all_support_type order by id) ;
--char
create or replace function func_char_cache(p char(30)) returns char as $$
begin
raise notice 'NOTICE: excute function...';
if p = '1_haha' then
gms_output.put_line('param:'||p);
end if;
return 'cache1';
end;
$$ language plpgsql stable result_cache;
/
select col_char,func_char_cache('add') from (select * from all_support_type order by id) ;
select col_char,func_char_cache(col_char) from (select * from all_support_type order by id) ;
--varchar2
create or replace function func_varchar2_cache(p varchar2(30)) returns varchar as $$
begin
raise notice 'NOTICE: excute function...';
if p = '1_haha' then
gms_output.put_line('param:'||p);
end if;
return 'cache1';
end;
$$ language plpgsql stable result_cache;
/
select col_varchar2,func_varchar2_cache('add') from (select * from all_support_type order by id) ;
select col_varchar2,func_varchar2_cache(col_varchar2) from (select * from all_support_type order by id) ;
--nvarchar2
create or replace function func_nvarchar2_cache(p nvarchar2(30)) returns varchar as $$
begin
raise notice 'NOTICE: excute function...';
if p = '1_haha' then
gms_output.put_line('param:'||p);
end if;
return 'cache1';
end;
$$ language plpgsql stable result_cache;
/
select col_varchar2,func_nvarchar2_cache('add') from (select * from all_support_type order by id) ;
select col_varchar2,func_nvarchar2_cache(col_varchar2) from (select * from all_support_type order by id) ;
--text
create or replace function func_text_cache(p text) returns varchar as $$
begin
raise notice 'NOTICE: excute function...';
if p = '1_haha' then
gms_output.put_line('param:'||p);
end if;
return 'cache1';
end;
$$ language plpgsql stable result_cache;
/
select col_text,func_text_cache('add') from (select * from all_support_type order by id) ;
select col_text,func_text_cache(col_text) from (select * from all_support_type order by id) ;

--teaddown
drop table if exists all_support_type;
drop function if exists func_smallint;
drop function if exists func_smallint_cache;
drop function if exists func_smallint_nopara;
drop function if exists func_smallint_cache;
drop function if exists func_varchar_cache;
drop function if exists func_int_cache;
drop function if exists func_bigint_cache;
drop function if exists func_number_cache;
drop function if exists func_numeric_cache;
drop function if exists func_float_cache;
drop function if exists func_double_precision_cache;
drop function if exists func_char_cache;
drop function if exists func_varchar2_cache;
drop function if exists func_nvarchar2_cache;
drop function if exists func_text_cache;
drop function if exists func_bool_cache;
drop function if exists func_timestamp_cache;
drop function if exists func_smallint_cache;
drop function if exists taxi_accum ;
drop function if exists pg_concat;
drop function if exists pg_concat_fin;
drop function if exists para_cache ;
drop function if exists para_cache ;
drop function if exists var_test1;
drop function if exists func_smallint_cache;
drop function if exists tran_update_func;
drop function if exists factorial(integer);
drop function if exists f_test;
drop function if exists pg_concat(text) cascade;
drop function if exists pg_concat(text,text) cascade;
